package org.anyline.simple.dm;

import org.anyline.data.jdbc.adapter.JDBCAdapter;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.entity.*;
import org.anyline.metadata.*;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.util.BasicUtil;
import org.anyline.util.ConfigTable;
import org.anyline.util.DateUtil;
import org.anyline.util.LogUtil;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

@SpringBootTest
public class DMTest {
    private Logger log = LoggerFactory.getLogger(DMTest.class);
    @Autowired
    private AnylineService service          ;
    @Autowired
    private JdbcTemplate jdbc               ;
    private Catalog catalog = null          ; //
    private Schema schema   = null          ; //
    private String table    = "CRM_USER"    ; // 表名

    public static void main(String[] args) {
        DataRow row = new OriginRow();
        row.put("projectName", "1");
        System.out.println(row.camel_());
    }
    @Test
    public void blob() throws Exception{
        service.truncate("CRM_USER");
        DataRow row = new DataRow();
        row.put("ID", System.currentTimeMillis());
        row.put("CODE", "c");
        row.put("b","blob String".getBytes());
        row.put("t","text String");
        service.insert( "CRM_USER", row);
        row = service.query("crm_user", "ORDER BY ID DESC");
        System.out.println(row);

    }
    @Test
    public void chars() throws Exception{
        Table table = service.metadata().table("table_char", false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("table_char");
        table.addColumn("id", "bigint").setPrimary(true).setAutoIncrement(true);
        table.addColumn("code", "char(20)");
        service.ddl().create(table);
        DataRow row = new DataRow();
        row.put("code", "中文AB1_?@");
        service.insert(table, row);
        DataSet set = service.querys(table);
        System.out.println(set);
    }

    @Test
    public void comment() throws Exception{
        Table table = service.metadata().table("table_comment", false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table<Table>("table_comment").setComment("表备注");
        table.addColumn("id", "bigint").setPrimary(true).setAutoIncrement(true);
        table.addColumn("code", "char(20)");
        service.ddl().create(table);
        table = service.metadata().table("table_comment");
        table.update().setComment("new comment");
        service.ddl().alter(table);
    }


    @Test
    public void batch1() throws Exception{
        List list=new ArrayList();
        for(int i=0;i <10;i ++) {
            Map map = new HashMap(1);
            map.put("CODE", i);
            map.put("NAME", "c"+i);
            list.add(map);
        }
        service.insert(500, "CRM_USER", list, "NAME","CODE");
        DataSet set = new DataSet();
        for(int i=0; i<10; i++){
            DataRow row = set.add();
            row.put("CODE", i);
            row.put("NAME", i+"c");
        }
        service.insert( 10,"CRM_USER", set, "NAME","CODE");
    }

    @Test
    public void batch() throws Exception{
        Table tab = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "bigint", false, null).setComment("主键").autoIncrement(true).primary(true);
        tab.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        tab.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        service.ddl().save(tab);
        long id = System.currentTimeMillis();
        DataSet set = new DataSet();
        //插入10行
        for(int i=0; i<10; i++){
            DataRow row = new DataRow();
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "N_"+i);
            set.add(row);
        }
        service.insert(table ,set);
        set = new DataSet();
        for(int i=0; i<15; i++){
            DataRow row = new DataRow();
            if(i<10) {
                row.put("ID", id + i);
            }
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "U_N"+i);
            set.add(row);
        }
        //save 15行，其中10行update 5行insert
        service.save(100,table ,set);
    }

    @Test
    public void metadata() throws Exception {
        service.metadata().views();
        LinkedHashMap<String, Table> tables = service.metadata().tables();
        for(Table table:tables.values()){
            LinkedHashMap<String, Column> columns = service.metadata().columns(table);
            System.out.println(columns);
        }
    }


        @Test
    public void databases() throws SQLException {
        LinkedHashMap<String, Database> databases = service.metadata().databases();
        for(Database database:databases.values()){
            log.warn("数据库:{},所属用户:{}", database.getName(), database.getUser());
        }
        String sql = "SELECT T.TABLE_NAME, T.OWNER, TC.COMMENTS\n" +
            "FROM SYS.ALL_ALL_TABLES T\n" +
            "LEFT JOIN SYS.ALL_TAB_COMMENTS TC  ON  TC.OWNER  = T.OWNER  AND TC.TABLE_NAME  = T.TABLE_NAME \n";
        System.out.println(service.querys(sql));
    }

    @Test
    public void timez() throws Exception{
        Table tab = service.metadata().table(this.table);
        if(null != tab){
            System.out.println("pk:"+tab.getPrimaryKey().getColumns());
            LinkedHashMap<String, Column> columns = tab.getColumns();
            for (Column column:columns.values()){
                System.out.println("\ncolumn:"+column.getName());
                System.out.println(column.getFullType());
                System.out.println("length:"+column.getLength()+" p:"+column.getPrecision()+" s:"+column.getScale());
            }
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("apr_time2", "TIMESTAMP(6)");
        tab.addColumn("apr_time3", "DATETIME(6)");
        tab.addColumn("apr_date", "date");
        tab.addColumn("apr_time6", "TIME");
        tab.addColumn("apr_time7", "TIME(6)");
        tab.addColumn("reg_time", "timestamp with time zone").setComment("注册时间");
        //tab.addColumn("upt_time", "timestamp WITH ZONE");
        tab.addColumn("apr_time", "TIMESTAMP_WITH_ZONE");
        tab.addColumn("apr_time1", "TIMESTAMP_WITH_LOCAL_ZONE");
        tab.addColumn("PRICE", "dec(10,2)");
        tab.addColumn("ID", "bigint").setPrimary(true);
        tab.addColumn("code", "varchar(10)").setComment("编码");
        service.ddl().create(tab);
        DataRow row = new DataRow();

        row.put("ID", System.currentTimeMillis());
        row.put("price",123.33);
        service.insert(tab, row);
        System.out.println(service.querys(tab));
        tab = service.metadata().table(this.table);
        System.out.println("pk:"+tab.getPrimaryKey().getColumns());
        LinkedHashMap<String, Column> columns = tab.getColumns();
        for (Column column:columns.values()){
            System.out.println("\ncolumn:"+column.getName());
            System.out.println(column.getFullType());
            System.out.println("length:"+column.getLength()+" p:"+column.getPrecision()+" s:"+column.getScale());
        }
    }

    @Test
    public void columns() throws Exception{
        /*Table tab = service.metadata().table(table);
        LinkedHashMap<String, Column> columns = tab.getColumns();
        for(Column column:columns.values()){
            System.out.println(column.getName()+":"+column.getComment());
        }*/
        LinkedHashMap<String, Column> cols = service.metadata().columns(table);
        for(Column column:cols.values()){
            System.out.println(column.getName()+":"+column.getComment());
        }
    }
    @Test
    public void test(){
        for(int i=11; i<10; i++) {
            Table table_new = service.metadata().table(table);
            table_new.addColumn("test_char"+i, "varchar(10)");
            try {
                table_new.setSchema(schema);
                service.ddl().save(table_new);
            } catch (Exception e) {
                log.error("errorInfo={}", e.getMessage());
            }
        }
    }

    @Test
    public void pk() throws Exception{


        Table table = service.metadata().table(this.table, false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("ID", "INT").setPrimary(true);
        table.addColumn("CODE", "int");
        table.addColumn("NAME", "VARCHAR2");
        service.ddl().create(table);
        table = service.metadata().table(this.table);

        PrimaryKey pk = new PrimaryKey();
        pk.setName("pk_code");
        pk.addColumn("CODE");
        table.setPrimaryKey(pk);
        //service.ddl().save(table);
        //service.ddl().add(pk);

    }

    @Test
    public void test_config(){
            ConfigStore configStores = new DefaultConfigStore();
            ConfigStore configStore = new DefaultConfigStore();
            ConfigStore configStore1 = new DefaultConfigStore();
            ConfigStore configStore2 = new DefaultConfigStore();
            configStore1.and("STATUS",1);
            configStore2.and("STATUS","2");
            configStore2.or("CREATE_BY","cxk");
            configStore.and(configStore1).or(configStore2);
            configStores.and(configStore);
        System.out.println(configStore.getRunText(service.runtime(), false));
        System.out.println(configStores.getRunText(service.runtime(), false));
    }
    @Test
    public void columnPk(){


        ConfigTable.IS_METADATA_AUTO_CHECK_COLUMN_PRIMARY = true;
        LinkedHashMap<String, Column> columns = service.metadata().columns(table);
        for(Column column:columns.values()){
            System.out.println(column.getName()+":"+column.isPrimaryKey());
            System.out.println(column.getMetadata());
        }
    }

    @Test
    public void pk1() throws Exception {
        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if (null != table) {
            service.ddl().drop(table);
        }
        //也可以直接删除(需要数据库支持 IF EXISTS)
        //service.ddl().drop(new Table(catalog, schema, this.table));

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNull(table);

        //定义表结构
        table = new Table(catalog, schema, this.table);
        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
        table.addColumn("ID", "INT", false, null).setComment("主键").setAutoIncrement(true);
        table.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        table.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        //默认当前时间 如果要适配多种数据库 用 SQL_BUILD_IN_VALUE.CURRENT_DATETIME
        table.addColumn("REG_TIME", "datetime").setComment("注册时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        table.addColumn("DATA_VERSION", "double", false, 1.1).setComment("数据版本");
        table.setPrimaryKey("ID");
        service.ddl().create(table);

        table = service.metadata().table(catalog, schema, this.table);
        LinkedHashMap<String,Column> columns = table.getColumns();
        for (Column column:columns.values()){
            System.out.println(column.getName());
            System.out.println(column.getFullType());
            System.out.println(column.isAutoIncrement());
            System.out.println(column.isPrimaryKey());
        }
    }
    @Test
    public void testupdate(){
        ConfigTable.IS_UPPER_KEY = false;
        DataRow row = new DataRow();
        row.put("NAME",System.currentTimeMillis());
        ServiceProxy.save("CRM_USER", row);
        row = ServiceProxy.query("CRM_USER");
        row = new OriginRow();
        row.put("id", 1);
        row.put("NAME",System.currentTimeMillis());
        ServiceProxy.save("CRM_USER", row);
    }
    @Test
    public void ddl() throws Exception{
        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != table){
            service.ddl().drop(table);
        }
        //也可以直接删除(需要数据库支持 IF EXISTS)
        //service.ddl().drop(new Table(catalog, schema, this.table));

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNull(table);

        //定义表结构
        table = new Table(catalog, schema, this.table);
        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
        table.addColumn("ID", "INT", false, null).setComment("主键").autoIncrement(true).primary(true);
        table.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        table.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        //默认当前时间 如果要适配多种数据库 用 SQL_BUILD_IN_VALUE.CURRENT_DATETIME
        table.addColumn("REG_TIME", "datetime").setComment("注册时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        table.addColumn("DATA_VERSION", "double", false, 1.1).setComment("数据版本");

        //创建表
        service.ddl().create(table);

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNotNull(table);
        LinkedHashMap<String, Column> columns = table.getColumns();
        for(Column column:columns.values()){
            System.out.println(column.getName() + ":" + column.isAutoIncrement());
        }

    }
    @Test
    public void time() throws Exception{
        //ConfigTable.IS_AUTO_CHECK_METADATA = true;
        ConfigTable.IS_SQL_DELIMITER_OPEN = true;
        Table table = service.metadata().table(this.table, false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("ID", "INT").setPrimary(true).setAutoIncrement(true);
        table.addColumn("CODE", "bigint");
        table.addColumn("TIME", "DATETIME");
        table.addColumn("T2", "TIMESTAMP");
        service.ddl().create(table);

        DataRow row = new DataRow();
        row.put("CODE", new BigDecimal(100));
        row.put("TIME", DateUtil.format());
        row.put("T2", DateUtil.format());
        service.insert(table, row);
    }
    @Test
    public void dml() throws Exception{
        DataRow row = new DataRow();
        DataSet set = new DataSet();
        for(int i=1; i<10; i++){
            row = new DataRow();
            //只插入NAME  ID自动生成 REG_TIME 默认当时时间
            row.put("NAME", "N"+i);
            set.add(row);
        }
        long qty = service.insert(table, set);
        log.warn(LogUtil.format("[批量插入][影响行数:{}][生成主键:{}]", 36), qty, set.getStrings("ID"));
        Assertions.assertEquals(qty , 9);

        row = new DataRow();
        row.put("NAME", "N");
        //当前时间，如果要适配多种数据库环境尽量用SQL_BUILD_IN_VALUE,如果数据库明确可以写以根据不同数据库写成: row.put("REG_TIME","${now()}"); sysdate,getdate()等等
        row.put("REG_TIME", JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        qty = service.insert(table, row);
        log.warn(LogUtil.format("[单行插入][影响行数:{}][生成主键:{}]", 36), qty, row.getId());
        Assertions.assertEquals(qty , 1);
        Assertions.assertNotNull(row.getId());

        //查询全部数据
        set = service.querys(table);
        log.warn(LogUtil.format("[query result][查询数量:{}]", 36), set.size());
        log.warn("[多行查询数据]:{}",set.toJSON());
        Assertions.assertEquals(set.size() , 10);

        //只查一行
        row = service.query(table);
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);
        Assertions.assertEquals(row.getId(), "1");

        //查最后一行
        row = service.query(table, "ORDER BY ID DESC");
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);
        Assertions.assertEquals(row.getInt("ID",10), 10);

        //更新
        //put覆盖了Map.put返回Object
        row.put("NAME", "SAVE NAME");

        //set由DataRow声明实现返回DataRow可以链式操作
        row.set("CODE", "SAVE CODE").set("DATA_VERSION", 1.2);

        //save根据是否有主键来判断insert | update
        //可以指定SAVE哪一列
        service.save(row, "NAME");
        service.save(row);
        row.put("NAME", "UPDATE NAME");

        /*
         * 注意这里的page一般不手工创建，而是通过AnylineController中的condition自动构造
         * service.querys("CRM_USER", condition(true, "ID:id","NAME:%name%", TYPE_CODE:[type]), "AGE:>=age");
         * true:表示分页 或者提供int 表示每页多少行
         * ID:表示数据表中的列
         * id:表示http提交的参数名
         * [type]:表示数组
         * */

        //分页查询
        //每页3行,当前第2页(下标从1开始)
        PageNavi page = new DefaultPageNavi(2, 3);

        //无论是否分页 都返回相同结构的DataSet
        set = service.querys(table, page);
        log.warn(LogUtil.format("[分页查询][共{}行 第{}/{}页]", 36), page.getTotalRow(), page.getCurPage(), page.getTotalPage());
        log.warn(set.toJSON());
        Assertions.assertEquals(page.getTotalPage() , 4);
        Assertions.assertEquals(page.getTotalRow() , 10);

        //模糊查询
        set = service.querys("CRM_USER", "NAME:%N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("CRM_USER", "NAME:%N");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("CRM_USER", "NAME:N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());

        //其他条件查询
        //in
        List<Integer> in = new ArrayList<>();
        in.add(1);
        in.add(2);
        in.add(3);
        ConfigStore condition = new DefaultConfigStore();
        condition.ands("ID", in);

        //not in
        condition.and(Compare.NOT_IN, "NAME", "N1");
        List<Integer> notin = new ArrayList<>();
        notin.add(10);
        notin.add(20);
        notin.add(30);
        condition.and(Compare.NOT_IN, "ID", notin);

        //between
        List<Integer> between = new ArrayList<>();
        between.add(1);
        between.add(10);
        condition.and(Compare.BETWEEN, "ID", between);

        // >=
        condition.and(Compare.GREAT_EQUAL, "ID", "1");

        //前缀
        condition.and(Compare.LIKE_PREFIX, "NAME", "N");

        set = service.querys("CRM_USER", condition);
        log.warn(LogUtil.format("[后台构建查询条件][result:{}]", 36), set.toJSON());
        Assertions.assertEquals(set.size() , 2);

        qty = service.count(table);
        log.warn(LogUtil.format("[总数统计][count:{}]", 36), qty);
        Assertions.assertEquals(qty , 10);

        //根据默认主键ID更新
        row.put("CODE",1001);
        //默认情况下 更新过的列 会参与UPDATE
        qty = service.update(row);
        log.warn(LogUtil.format("[根据主键更新内容有变化的化][count:{}]", 36), qty);


        //根据临时主键更新,注意这里更改了主键后ID就成了非主键，但未显式指定更新ID的情况下,ID不参与UPDATE
        row.setPrimaryKey("NAME");
        qty = service.update(row);
        log.warn(LogUtil.format("[根据临时主键更新][count:{}]", 36), qty);

        try {
            //显示指定更新列的情况下才会更新主键与默认主键,自增列会抛出异常：试图修改自增列[ID]
            qty = service.update(row, "NAME", "CODE", "ID");
        }catch (Exception e){
            log.error(e.toString());
        }
        log.warn(LogUtil.format("[更新指定列][count:{}]", 36), qty);

        //根据条件更新
        ConfigStore store = new DefaultConfigStore();
        store.and(Compare.GREAT, "ID", "1")
                
                .and(" CODE > 1")
                .and("NAME IS NOT NULL");
        qty = service.update(row, store);
        log.warn(LogUtil.format("[根据条件更新][count:{}]", 36), qty);


        qty = service.delete(set);
        log.warn("[根据ID删除集合][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());

        //根据主键删除
        qty = service.delete(row);
        log.warn("[根据ID删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, 1);

        set = service.querys(table, "ID:1");
        qty = service.delete(table, "ID","1");
        log.warn("[根据条件删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());

        set = service.querys(table, "ID IN(2,3)");
        qty = service.deletes(table, "ID","2","3");
        log.warn("[根据条件删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());
    }

    @Test
    public void float_value() throws Exception{
        //ConfigTable.IS_AUTO_CHECK_METADATA = true;
        ConfigTable.IS_SQL_DELIMITER_OPEN = true;
        Table table = service.metadata().table(this.table, false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("ID", "INT").setPrimary(true).setAutoIncrement(true);
        table.addColumn("CODE", "bigint");
        table.addColumn("TIME", "DATETIME");
        table.addColumn("T2", "TIMESTAMP");
        table.addColumn("FV", "REAL");
        service.ddl().create(table);

        DataRow row = new DataRow();
        row.put("CODE", new BigDecimal(100));
        row.put("TIME", DateUtil.format());
        row.put("T2", DateUtil.format());
        row.put("FV", 0.1234556);
        service.insert(table, row);
        DataSet set = service.querys(table);
        System.out.println(set);
    }

    @Test
    public void help() throws Exception{
        Connection con = jdbc.getDataSource().getConnection();
        System.out.println("\n--------------[metadata]------------------------");
        System.out.println("catalog:"+con.getCatalog());
        System.out.println("schema:"+con.getSchema());
        ResultSet set = con.getMetaData().getTables(null, null, table, "TABLE".split(","));
        ResultSetMetaData md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[table metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",20) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }
        set = jdbc.getDataSource().getConnection().getMetaData().getColumns(null, null, null, null);
        md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[column metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",35) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }

    }
}
